﻿using System;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace VSM.Utils
{
    public class NpoiExport : IDisposable
    {
        const int MaximumNumberOfRowsPerSheet = 65500;
        const int MaximumSheetNameLength = 25;
        protected XSSFWorkbook XWorkbook { get; set; }
        protected HSSFWorkbook hWorkbook { get; set; }
        protected ISheet iSheet { get; set; }
        protected string _extension = "xls";

        public NpoiExport()
        {
            this.hWorkbook = new HSSFWorkbook(); // excel 97-2003
            this.XWorkbook = new XSSFWorkbook(); // xlsx
        }

        public NpoiExport(string fileName)
        {
            string[] extFileName = fileName.Split('.');
            if (extFileName.Length > 1)
            {
                using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    if (extFileName[1].ToLower().Equals(Utils.AppConstants.EXTENSION_XLSX))
                    {
                        _extension = Utils.AppConstants.EXTENSION_XLSX;
                        this.XWorkbook = new XSSFWorkbook(file);
                    }
                    else
                        this.hWorkbook = new HSSFWorkbook(file);
                }
            }
            else
            {
                this.hWorkbook = new HSSFWorkbook(); // excel 97-2003
                this.XWorkbook = new XSSFWorkbook(); // xlsx
            }
        }

        public static DataTable ReadExcel(string path, string sheetName)
        {
            XSSFWorkbook hssfwb;
            DataTable tb = new DataTable();
            tb.Columns.Add("ItemCode");
            tb.Columns.Add("Quantity");
            tb.Columns.Add("DisCount");
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfwb = new XSSFWorkbook(file);
            }
            ISheet sheet = hssfwb.GetSheet(sheetName);
            if (sheet != null)
            {
                for (int row = 1; row <= sheet.LastRowNum; row++)
                {
                    if (sheet.GetRow(row) != null)
                    {
                        try
                        {
                            DataRow r = tb.NewRow();
                            r[sheet.GetRow(0).GetCell(0).StringCellValue] = sheet.GetRow(row).GetCell(0).StringCellValue;
                            r[sheet.GetRow(0).GetCell(1).StringCellValue] = sheet.GetRow(row).GetCell(1).NumericCellValue;
                            r[sheet.GetRow(0).GetCell(2).StringCellValue] = sheet.GetRow(row).GetCell(2).NumericCellValue;
                            tb.Rows.Add(r);
                        }
                        catch
                        {
                        }
                    }
                }
            }
            return tb;
        }

        protected string EscapeSheetName(string sheetName)
        {
            var escapedSheetName = sheetName
                                        .Replace("/", "-")
                                        .Replace("\\", " ")
                                        .Replace("?", string.Empty)
                                        .Replace("*", string.Empty)
                                        .Replace("[", string.Empty)
                                        .Replace("]", string.Empty)
                                        .Replace(":", string.Empty);

            if (escapedSheetName.Length > MaximumSheetNameLength)
                escapedSheetName = escapedSheetName.Substring(0, MaximumSheetNameLength);

            return escapedSheetName;
        }

        protected ISheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData, string sheetName, ICellStyle headerRowStyle)
        {
            var sheet = this.hWorkbook.CreateSheet(EscapeSheetName(sheetName));

            // Create the header row
            var row = sheet.CreateRow(0);

            for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
            {
                var cell = row.CreateCell(colIndex);
                cell.SetCellValue(exportData.Columns[colIndex].ColumnName);

                if (headerRowStyle != null)
                    cell.CellStyle = headerRowStyle;
            }

            return sheet;
        }

        public void ExportDataTableToWorkbook(DataTable exportData, string sheetName)
        {
            // Create the header row cell style
            var headerLabelCellStyle = this.hWorkbook.CreateCellStyle();
            var headerLabelFont = this.hWorkbook.CreateFont();
            headerLabelFont.Boldweight = (short)FontBoldWeight.Bold;
            headerLabelCellStyle.SetFont(headerLabelFont);

            var sheet = CreateExportDataTableSheetAndHeaderRow(exportData, sheetName, headerLabelCellStyle);
            var currentNPOIRowIndex = 1;
            var sheetCount = 1;

            for (var rowIndex = 0; rowIndex < exportData.Rows.Count; rowIndex++)
            {
                if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)
                {
                    sheetCount++;
                    currentNPOIRowIndex = 1;

                    sheet = CreateExportDataTableSheetAndHeaderRow(exportData,
                                                                   sheetName + " - " + sheetCount,
                                                                   headerLabelCellStyle);
                }

                var row = sheet.CreateRow(currentNPOIRowIndex++);

                for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
                {
                    var cell = row.CreateCell(colIndex);
                    cell.SetCellValue(exportData.Rows[rowIndex][colIndex].ToString());
                }
            }
        }

        public byte[] GetBytes()
        {
            using (var buffer = new MemoryStream())
            {
                this.hWorkbook.Write(buffer);
                return buffer.GetBuffer();
            }
        }

        
        //public bool ReadExcelExistedFile(string fileName)
        //{
        //    try
        //    {
        //        using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
        //        {
        //            this.hWorkbook = new HSSFWorkbook(file);
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        return false;
        //    }

        //    return true;
        //}

        public bool GetSheet(string sheetName)
        {
            try
            {
                if(_extension.Equals(Utils.AppConstants.EXTENSION_XLSX))
                    iSheet = this.XWorkbook.GetSheet(sheetName);
                else
                    iSheet = this.hWorkbook.GetSheet(sheetName); 
            }
            catch (Exception ex)
            {
                return false;
            }

            return true;
        }

        public bool SetValue(string key, object obj)
        { 
            try
            {
                bool smooth = false;
                for (int row = 0; row <= iSheet.LastRowNum; row++)
                {
                    if (smooth) break;
                    IRow dataRow = iSheet.GetRow(row);

                    if (dataRow != null) //null is when the row only contains empty cells 
                    {

                        foreach (ICell cell in dataRow.Cells)
                        {
                            if (cell.CellType != CellType.String) continue;
                            if (cell.StringCellValue.Equals(key))
                            {
                                if(obj.GetType() == typeof(System.Double))
                                    cell.SetCellValue((double)obj);
                                else
                                    cell.SetCellValue(obj.ToString());
                                smooth = true;
                                break;
                            }
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public bool SetValues(Dictionary<string, object> dic)
        {
            try
            {
                // Loop over pairs with foreach
                foreach (KeyValuePair<string, object> pair in dic)
                {
                    SetValue(pair.Key, pair.Value);
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public bool SetValueByPosition(int colIndex, int rowIndex, string asValue)
        {
            try
            {
                IRow rowSource = iSheet.GetRow(rowIndex);
                ICell cellSource = rowSource.GetCell(colIndex);
                cellSource.SetCellValue(asValue);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public bool SetValuesByPosition(int startColIndex, int rowIndex, char[] acValues)
        {
            try
            {
                for (int i = 0; i < acValues.Length; i++)
                {
                    SetValueByPosition(startColIndex + i, rowIndex, acValues[i].ToString());
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public bool InsertRow(DataTable dtContents, Int32 fromRowIndex, Int32 rowCount)
        {
            try
            {
                double ldblQuantity = 0, ldblBefTaxAmount = 0, ldblTaxAmount = 0, ldblTotalAmount = 0;
                IRow rowSource = iSheet.GetRow(fromRowIndex);

                // Insert row
                if(rowCount > 1) iSheet.ShiftRows(fromRowIndex+1, iSheet.LastRowNum, rowCount - 1);

                for (Int32 i = 0; i < rowCount; i++ )
                {
                    DataRow dr = dtContents.Rows[i];
                    IRow dataRow = iSheet.CreateRow(fromRowIndex);

                    // set format new row from default row
                    for (Int32 colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                    {
                        ICell cellSource = rowSource.GetCell(colIndex);
                        ICell cellnew = dataRow.CreateCell(colIndex);
                        if (cellSource != null && cellSource.CellStyle != null)
                            cellnew.CellStyle = cellSource.CellStyle;
                    }

                    double Quantity = double.Parse(dr["Quantity"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat);
                    ldblQuantity += Quantity;
                    double BefTaxAmount = double.Parse(dr["BefTaxAmount"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat);
                    ldblBefTaxAmount += BefTaxAmount;
                    double TaxAmount = double.Parse(dr["TaxAmount"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat);
                    ldblTaxAmount += TaxAmount;
                    double TotalAmount = double.Parse(dr["TotalAmount"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat);
                    ldblTotalAmount += TotalAmount;

                    dataRow.Cells[1].SetCellValue(dr["ProductName"].ToString()); // Loại (Product name)
                    //dataRow.Cells[2].SetCellValue(dr["U_Product"].ToString()); // Loại (Product code)
                    dataRow.Cells[4].SetCellValue(dr["ColorName"].ToString()); // Màu (Color code)

                    dataRow.Cells[7].SetCellValue(Quantity); // Số lượng (Quantity)
                    dataRow.Cells[8].SetCellValue("H1"); // Lũy tiến
                    dataRow.Cells[10].SetCellValue(double.Parse(dr["Price"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat)); // Đơn giá tiêu chuẩn (Price)
                    dataRow.Cells[12].SetCellValue(double.Parse(dr["DiscAmount"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat)); // Giảm giá
                    dataRow.Cells[15].SetCellValue(double.Parse(dr["AftDisPrice"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat)); // Đơn giá thực tế 
                    dataRow.Cells[17].SetCellValue(BefTaxAmount); // Thành tiền
                    dataRow.Cells[19].SetCellValue(TaxAmount); // Thuế GTGT
                    dataRow.Cells[21].SetCellValue(TotalAmount); // Tổng cộng

                    fromRowIndex++;
                }

                
                for(int j = 46; j > fromRowIndex; j--)
                {
                    iSheet.GetRow(j).Height = -1;
                }

                // Update Total
                IRow rowTotal = iSheet.GetRow(fromRowIndex +1);
                rowTotal.Cells[7].SetCellValue(ldblQuantity);
                rowTotal.Cells[17].SetCellValue(ldblBefTaxAmount);
                rowTotal.Cells[19].SetCellValue(ldblTaxAmount);
                rowTotal.Cells[21].SetCellValue(ldblTotalAmount);
                // Read Amount by words
                GeneralFunctions GF = new GeneralFunctions();
                SetValue("AmountbyWords", GF.Amount2Words((long)ldblTotalAmount, " đồng"));
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public bool InsertTransferDetails(DataTable dtContents, Int32 fromRowIndex, Int32 rowCount)
        {
            try
            {
                double ldblQuantity = 0, ldblTaxAmount = 0, ldblTotalAmount = 0;
                IRow rowSource = iSheet.GetRow(fromRowIndex);

                // Insert row
                if (rowCount > 1) iSheet.ShiftRows(fromRowIndex + 1, iSheet.LastRowNum, rowCount - 1);

                for (Int32 i = 0; i < rowCount; i++)
                {
                    DataRow dr = dtContents.Rows[i];
                    IRow dataRow = iSheet.CreateRow(fromRowIndex);

                    // set format new row from default row
                    for (Int32 colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                    {
                        ICell cellSource = rowSource.GetCell(colIndex);
                        ICell cellnew = dataRow.CreateCell(colIndex);
                        if (cellSource != null && cellSource.CellStyle != null)
                            cellnew.CellStyle = cellSource.CellStyle;
                    }

                    double Quantity = double.Parse(dr["OpenQty"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat);
                    ldblQuantity += Quantity;

                    double TaxAmount = double.Parse(dr["TaxAmount"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat);
                    ldblTaxAmount += TaxAmount;
                    double TotalAmount = double.Parse(dr["TotalAmount"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat);
                    ldblTotalAmount += TotalAmount;

                    dataRow.Cells[0].SetCellValue(i + 1);
                    //dataRow.Cells[1].SetCellValue(dr["Model"].ToString()); // Loại (Product name)
                    dataRow.Cells[1].SetCellValue("Mitsubishi"); // Loại (Product name)
                    dataRow.Cells[4].SetCellValue(dr["ProductName"].ToString()); // Loại (Product code)
                    dataRow.Cells[9].SetCellValue(dr["Color"].ToString()); // Màu (Color code)
                    dataRow.Cells[10].SetCellValue(dr["DistNumber"].ToString()); // Vin Number
                    dataRow.Cells[19].SetCellValue(dr["Notes"].ToString()); // Engine Number
                    dataRow.Cells[24].SetCellValue(double.Parse(dr["Price"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat)); // (WholeSalePrice)
                    var cra = new NPOI.SS.Util.CellRangeAddress(fromRowIndex, fromRowIndex, 24, 26);
                    iSheet.AddMergedRegion(cra);
                    
                    dataRow.Cells[27].SetCellValue(TaxAmount); // Thuế GTGT
                    cra = new NPOI.SS.Util.CellRangeAddress(fromRowIndex, fromRowIndex, 27, 29);
                    iSheet.AddMergedRegion(cra);

                    dataRow.Cells[30].SetCellValue(TotalAmount); // Tổng cộng
                    cra = new NPOI.SS.Util.CellRangeAddress(fromRowIndex, fromRowIndex, 30, 32);
                    iSheet.AddMergedRegion(cra);

                    fromRowIndex++;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public bool ExportSalesQuotation(DataTable dtContents, Int32 fromRowIndex, Int32 rowCount)
        {
            try
            {
                double orderTotalBeforeDiscount = 0, taxTotal = 0, discountAmt = 0,totalQty=0;
                IRow rowSource = iSheet.GetRow(fromRowIndex);

                // Insert row
                if (rowCount > 1) iSheet.ShiftRows(fromRowIndex + 1, iSheet.LastRowNum, rowCount - 1);

                for (Int32 i = 0; i < rowCount; i++)
                {
                    DataRow dr = dtContents.Rows[i];
                    IRow dataRow = iSheet.CreateRow(fromRowIndex);

                    // set format new row from default row
                    for (Int32 colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                    {
                        ICell cellSource = rowSource.GetCell(colIndex);
                        ICell cellnew = dataRow.CreateCell(colIndex);
                        if (cellSource != null && cellSource.CellStyle != null)
                            cellnew.CellStyle = cellSource.CellStyle;
                    }
                    double total = double.Parse(dr["TotalAfDisc"].ToString());
                    orderTotalBeforeDiscount += total;
                    double tax = double.Parse(dr["VatSum"].ToString());
                    taxTotal += tax;
                    totalQty += double.Parse(dr["Quantity"].ToString());
                    discountAmt = ((double.Parse(dr["DiscPrcnt"].ToString())) * double.Parse(dr["PriceBefDi"].ToString())) / 100;
                    dataRow.Cells[0].SetCellValue(double.Parse(dr["No"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat));
                    dataRow.Cells[1].SetCellValue(dr["Dscription"].ToString());
                    dataRow.Cells[2].SetCellValue(dr["ItemCode"].ToString());
                    dataRow.Cells[3].SetCellValue(dr["unitMsr"].ToString());
                    dataRow.Cells[4].SetCellValue(double.Parse(dr["Quantity"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat));
                    dataRow.Cells[5].SetCellValue(dr["PriceBefDi"].ToString());
                    dataRow.Cells[6].SetCellValue(discountAmt.ToString("#,##0"));
                    dataRow.Cells[7].SetCellValue(double.Parse(dr["VatPrcnt"].ToString()));
                    dataRow.Cells[8].SetCellValue(dr["Total"].ToString());
                    fromRowIndex++;
                }
                SetValue("paTotalQty", totalQty);
                // Update Total
                IRow rowTotal = iSheet.GetRow(fromRowIndex + 1);
                rowTotal.Cells[8].SetCellValue(orderTotalBeforeDiscount);
                rowTotal = iSheet.GetRow(fromRowIndex + 2);
                rowTotal.Cells[8].SetCellValue(taxTotal);
                rowTotal = iSheet.GetRow(fromRowIndex + 3);
                rowTotal.Cells[8].SetCellValue(orderTotalBeforeDiscount + taxTotal);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public bool ExportQT(DataTable dtContents, Int32 fromRowIndex, Int32 rowCount)
        {
            try
            {
                double orderTotalBeforeDiscount = 0, taxTotal = 0, discountAmt = 0, totalQty = 0;
                IRow rowSource = iSheet.GetRow(fromRowIndex);

                // Insert row
                if (rowCount > 1) iSheet.ShiftRows(fromRowIndex + 1, iSheet.LastRowNum, rowCount - 1);

                for (Int32 i = 0; i < rowCount; i++)
                {
                    DataRow dr = dtContents.Rows[i];
                    IRow dataRow = iSheet.CreateRow(fromRowIndex);

                    // set format new row from default row
                    for (Int32 colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                    {
                        ICell cellSource = rowSource.GetCell(colIndex);
                        ICell cellnew = dataRow.CreateCell(colIndex);
                        if (cellSource != null && cellSource.CellStyle != null)
                            cellnew.CellStyle = cellSource.CellStyle;
                    }
                    double total = double.Parse(dr["LineTotal"].ToString());
                    orderTotalBeforeDiscount += total;
                    double vatPrcnt = 0;
                    vatPrcnt = double.Parse(dr["VatPrcnt"].ToString());
                    double tax = (total * vatPrcnt) / 100;
                    taxTotal += tax;
                    totalQty += double.Parse(dr["Quantity"].ToString());
                    discountAmt = ((double.Parse(dr["DiscPrcnt"].ToString())) * double.Parse(dr["PriceBefDi"].ToString())) / 100;
                    dataRow.Cells[0].SetCellValue(double.Parse(dr["No"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat));
                    dataRow.Cells[1].SetCellValue(dr["Dscription"].ToString());
                    dataRow.Cells[2].SetCellValue(dr["ItemCode"].ToString());
                    dataRow.Cells[3].SetCellValue(dr["unitMsr"].ToString());
                    dataRow.Cells[4].SetCellValue(double.Parse(dr["Quantity"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat));
                    dataRow.Cells[5].SetCellValue(dr["PriceBefDi"].ToString());
                    dataRow.Cells[6].SetCellValue(discountAmt.ToString("#,##0"));
                    dataRow.Cells[7].SetCellValue(double.Parse(dr["VatPrcnt"].ToString()));
                    total = total + tax;
                    dataRow.Cells[8].SetCellValue(total.ToString("#,##0"));
                    fromRowIndex++;
                }
                SetValue("paTotalQty", totalQty);
                // Update Total
                IRow rowTotal = iSheet.GetRow(fromRowIndex + 1);
                rowTotal.Cells[8].SetCellValue(orderTotalBeforeDiscount.ToString("#,##0"));
                rowTotal = iSheet.GetRow(fromRowIndex + 2);
                rowTotal.Cells[8].SetCellValue(taxTotal.ToString("#,##0"));
                rowTotal = iSheet.GetRow(fromRowIndex + 3);
                double paymentTotal = orderTotalBeforeDiscount + taxTotal;
                rowTotal.Cells[8].SetCellValue(paymentTotal.ToString("#,##0"));
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public bool ExportXK(DataTable dtContents, Int32 fromRowIndex, Int32 rowCount)
        {
            try
            {
                double  discountAmt = 0, totalQty = 0;
                IRow rowSource = iSheet.GetRow(fromRowIndex);

                // Insert row
                if (rowCount > 1) iSheet.ShiftRows(fromRowIndex + 1, iSheet.LastRowNum, rowCount - 1);

                for (Int32 i = 0; i < rowCount; i++)
                {
                    DataRow dr = dtContents.Rows[i];
                    IRow dataRow = iSheet.CreateRow(fromRowIndex);

                    // set format new row from default row
                    for (Int32 colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                    {
                        ICell cellSource = rowSource.GetCell(colIndex);
                        ICell cellnew = dataRow.CreateCell(colIndex);
                        if (cellSource != null && cellSource.CellStyle != null)
                            cellnew.CellStyle = cellSource.CellStyle;
                    }
                    totalQty += double.Parse(dr["Quantity"].ToString());
                    discountAmt = ((double.Parse(dr["DiscPrcnt"].ToString())) * double.Parse(dr["PriceBefDi"].ToString())) / 100;
                    dataRow.Cells[0].SetCellValue(double.Parse(dr["No"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat));
                    dataRow.Cells[1].SetCellValue(dr["Dscription"].ToString());
                    dataRow.Cells[2].SetCellValue(dr["ItemCode"].ToString());
                    dataRow.Cells[3].SetCellValue(dr["unitMsr"].ToString());
                    dataRow.Cells[5].SetCellValue(double.Parse(dr["Quantity"].ToString(), CultureInfo.GetCultureInfo("de-DE").NumberFormat));
                    fromRowIndex++;
                }
                SetValue("paTotalQty", totalQty);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public byte[] GetFileHasChanged()
        {
            // Forcing formula recalculation...
            iSheet.ForceFormulaRecalculation = true;
            //
            MemoryStream ms = new MemoryStream();
            // Writing the workbook content to the FileStream...
            if(_extension.Equals(Utils.AppConstants.EXTENSION_XLSX))
                this.XWorkbook.Write(ms);
            else
                this.hWorkbook.Write(ms);
            return ms.ToArray();
        }

        public void CreateFile(string newFileName)
        {
            using (FileStream stream = File.Create(newFileName, 0x400))
            {
                byte[] bytes = GetFileHasChanged();
                stream.Write(bytes, 0, bytes.Length);
            }
        }

        public void Dispose()
        {
        }
    }
}